const router = require('koa-router')()

router.prefix('/information')
// 获取信息
router.get("/getGrade", async (ctx, next) => {
    console.log(ctx.query);
    let query = ctx.query;
    let sql = "select grade.grade_id, grade.grade_name, grade.grade_size, grade.class_number, institute.institute_id, institute.institute_name from grade join institute on grade.institute_id = institute.institute_id limit ? offset ?";
    let res = await ctx.db.EXCUTE(sql, [1 * query.pageSize, query.pageSize * (query.pageNum - 1)])
    console.log(res);
    let numSql = 'select COUNT(*) as num from grade'
    let numRes = await ctx.db.EXCUTE(numSql, [])
    console.log(numRes[0].num);

    ctx.body = { code: 200, msg: "查询成功", data: { items: res, total: numRes[0].num } }
})
// 搜索信息
router.post('/searchGrade', async (ctx, next) => {
    let query = ctx.query;
    let data = ctx.request.body;
    if (data.institute_id == '') {
        let sql = `select grade.grade_id, grade.grade_name, grade.grade_size, grade.class_number, institute.institute_id, institute.institute_name from grade join institute on grade.institute_id = institute.institute_id where grade.grade_name LIKE '%${data.keyword}%'`;
        let res = await ctx.db.EXCUTE(sql, [])
        let list = res.slice((query.pageNum - 1) * query.pageSize, query.pageNum * query.pageSize)

        ctx.body = { code: 200, msg: "查询成功", data: { items: list, total: res.length } }
    } else {
        let sql = `select grade.grade_id, grade.grade_name, grade.grade_num, grade.class_num, institute.institute_id, institute.institute_name from grade join institute on grade.institute_id = institute.institute_id where grade.grade_name LIKE '%${data.keyword}%' and grade.institute_id=?`;
        let res = await ctx.db.EXCUTE(sql, [data.institute_id])
        let list = res.slice((query.pageNum - 1) * query.pageSize, query.pageNum * query.pageSize)

        ctx.body = { code: 200, msg: "查询成功", data: { items: list, total: res.length } }
    }
})
// 添加年级
router.post('/addGrade', async (ctx, next) => {
    let data = ctx.request.body
    let Fsql = 'select grade_name from grade where institute_id=? and grade_name=?'
    let Fres = await ctx.db.EXCUTE(Fsql, [data.institute_id, data.grade_name])
    if (Fres.length == 0) {
        // 此处的创建学生ID传入的为1
        let id = ctx.snowFlake.createSnow(3);
        let sql = 'insert into grade (institute_id,grade_id,grade_name,grade_num,class_num) values (?,?,?,?,?)'
        let res = await ctx.db.EXCUTE(sql, [data.institute_id, id, data.grade_name, 0, 0])
        console.log(res);
        ctx.body = { code: 200, msg: "添加成功" }
    } else {
        ctx.body = { code: 201, msg: '该学院已经存在该学院名，请更换后再添加' }
    }
})
// 删除年级
router.post('/deleteGrade', async (ctx, next) => {
    let data = ctx.request.body
    let sql = 'select COUNT(*) as num from class where grade_id=?'
    let res = await ctx.db.EXCUTE(sql, [data.grade_id])
    if (res[0].num == 0) {
        let sql = 'delete from grade where grade_id=?'
        let res = await ctx.db.EXCUTE(sql, [data.grade_id])
        ctx.body = { code: 200, msg: '删除成功' }
    } else {
        ctx.body = { code: 204, msg: '该年级还存在班级，暂时不能删除' }
    }
})
// 修改年级信息
router.post('/editGrade', async (ctx, next) => {
    let data = ctx.request.body;
    let hassql = 'select grade_name from grade where grade_name=? and institute_id=?'
    let hasRes = await ctx.db.EXCUTE(hassql, [data.grade_name, data.institute_id])
    if (hasRes.length > 0) {
        ctx.body = { code: 201, msg: '该学院已存在' }
    } else {
        let sql = 'update grade set grade_name=? where grade_id=?'
        let res = await ctx.db.EXCUTE(sql, [data.grade_name, data.grade_id])
        console.log(res);
        ctx.body = { code: 200, msg: '修改成功' }
    }
})

module.exports = router